package cn.easybuy.dao.impl;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

import cn.easybuy.dao.BaseDao;
import cn.easybuy.dao.ProductDao;
import cn.easybuy.entity.Product;
import cn.easybuy.util.PageUtil;

public class ProductDaoImpl extends BaseDao implements ProductDao {

	private Connection conn;
	private PreparedStatement pstmt;
	private ResultSet rs;
	
	@Override
	public PageUtil<Product> findProductsByCategoryId(int categoryId,int categoryLevel,int beginIndex,int pageSize) {
		PageUtil<Product> pageUtil = new PageUtil<>();
		pageUtil.setPageSize(pageSize);
		List<Product> list = new ArrayList<>();
		try {
			conn = super.getConnection();
			
			//查询内容数据
			String sql = "select id,name,description,price,stock,categoryLevel1Id,categoryLevel2Id,categoryLevel3Id,fileName,isDelete "
					+ " from easybuy_product where isDelete=0 ";
			
			switch (categoryLevel) {
			case 1:
				sql += "and categoryLevel1Id=? ";
				break;
			case 2:
				sql += "and categoryLevel2Id=? ";
				break;
			case 3:
				sql += "and categoryLevel3Id=? ";
				break;
			}
			
			sql += "limit ?,?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, categoryId);
			pstmt.setObject(2, beginIndex);
			pstmt.setObject(3, pageSize);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				Product product = new Product();
		        product.setId(rs.getInt("id"));
		        product.setName(rs.getString("name"));
		        product.setDescription(rs.getString("description"));
		        product.setPrice(rs.getFloat("price"));
		        product.setStock(rs.getInt("stock"));
		        product.setCategoryLevel1Id(rs.getInt("categoryLevel1Id"));
		        product.setCategoryLevel2Id(rs.getInt("categoryLevel2Id"));
		        product.setCategoryLevel3Id(rs.getInt("categoryLevel3Id"));
		        product.setFileName(rs.getString("fileName"));
				list.add(product);
			}
			//将集合数据存到pageUtil里
			pageUtil.setList(list);
			
			int totalCount = 0;
			//查询记录数
			String sqlCount = "select count(1) "
					+ " from easybuy_product where isDelete=0 ";
			
			switch (categoryLevel) {
			case 1:
				sqlCount += "and categoryLevel1Id=? ";
				break;
			case 2:
				sqlCount += "and categoryLevel2Id=? ";
				break;
			case 3:
				sqlCount += "and categoryLevel3Id=? ";
				break;
			}
			
			pstmt = conn.prepareStatement(sqlCount);
			pstmt.setObject(1, categoryId);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			pageUtil.setTotalCount(totalCount);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		return pageUtil;
	}

	@Override
	public Product getProductById(int id) {
		Product product = null;
		try {
			conn = super.getConnection();
			
			//查询内容数据
			String sql = "select id,name,description,price,stock,categoryLevel1Id,categoryLevel2Id,categoryLevel3Id,fileName,isDelete "
					+ " from easybuy_product where id=? ";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, id);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				product = new Product();
		        product.setId(rs.getInt("id"));
		        product.setName(rs.getString("name"));
		        product.setDescription(rs.getString("description"));
		        product.setPrice(rs.getFloat("price"));
		        product.setStock(rs.getInt("stock"));
		        product.setCategoryLevel1Id(rs.getInt("categoryLevel1Id"));
		        product.setCategoryLevel2Id(rs.getInt("categoryLevel2Id"));
		        product.setCategoryLevel3Id(rs.getInt("categoryLevel3Id"));
		        product.setFileName(rs.getString("fileName"));
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		
		return product;
	}
	
	
	
	
	public static void main(String[] args) {
		ProductDaoImpl dao = new ProductDaoImpl();
		PageUtil<Product> pageUtil = dao.findProductsByCategoryId(681, 1, 0, 4);
		System.out.println(pageUtil.getList().size());
	}

	@Override
	public int subStock(int productId, int quantity) {
		int result=0;
    	try {
    		
    		conn = super.getConnection();
    		
    		String sql=" update easybuy_product set stock=stock-? where id=? ";
    		
    		pstmt = conn.prepareStatement(sql);
    		
    		pstmt.setObject(1, quantity);
    		pstmt.setObject(2,productId);

    		result = pstmt.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	super.closeAll(conn, pstmt, rs);
        }
    	return result;
	}

	@Override
	public PageUtil<Product> findAllProducts(int beginIndex, int pageSize) {
		PageUtil<Product> pageUtil = new PageUtil<>();
		pageUtil.setPageSize(pageSize);
		List<Product> list = new ArrayList<>();
		try {
			conn = super.getConnection();
			
			//查询内容数据
			String sql = "select id,name,description,price,stock,categoryLevel1Id,categoryLevel2Id,categoryLevel3Id,fileName,isDelete "
					+ " from easybuy_product where isDelete=0 ";
			
			sql += "limit ?,?";
			
			pstmt = conn.prepareStatement(sql);
			pstmt.setObject(1, beginIndex);
			pstmt.setObject(2, pageSize);
			rs = pstmt.executeQuery();
			
			while (rs.next()) {
				Product product = new Product();
		        product.setId(rs.getInt("id"));
		        product.setName(rs.getString("name"));
		        product.setDescription(rs.getString("description"));
		        product.setPrice(rs.getFloat("price"));
		        product.setStock(rs.getInt("stock"));
		        product.setCategoryLevel1Id(rs.getInt("categoryLevel1Id"));
		        product.setCategoryLevel2Id(rs.getInt("categoryLevel2Id"));
		        product.setCategoryLevel3Id(rs.getInt("categoryLevel3Id"));
		        product.setFileName(rs.getString("fileName"));
				list.add(product);
			}
			//将集合数据存到pageUtil里
			pageUtil.setList(list);
			
			int totalCount = 0;
			//查询记录数
			String sqlCount = "select count(1) "
					+ " from easybuy_product where isDelete=0 ";
			
			pstmt = conn.prepareStatement(sqlCount);
			rs = pstmt.executeQuery();
			
			if (rs.next()) {
				totalCount = rs.getInt(1);
			}
			pageUtil.setTotalCount(totalCount);
			
		} catch (Exception e) {
			e.printStackTrace();
		} finally {
        	super.closeAll(conn, pstmt, rs);
        }
		return pageUtil;
	}

	@Override
	public int add(Product product) {
		int result=0;
    	try {
    		
    		conn = super.getConnection();
    		
    		String sql="insert into easybuy_product(name,description,price,stock,categoryLevel1Id,categoryLevel2Id,categoryLevel3Id,fileName,isDelete) values(?,?,?,?,?,?,?,?,?) ";
    		
    		pstmt = conn.prepareStatement(sql);
    		
    		pstmt.setObject(1, product.getName());
    		pstmt.setObject(2, product.getDescription());
    		pstmt.setObject(3, product.getPrice());
    		pstmt.setObject(4, product.getStock());
    		pstmt.setObject(5, product.getCategoryLevel1Id());
    		
    		pstmt.setObject(6, product.getCategoryLevel2Id());
    		pstmt.setObject(7, product.getCategoryLevel3Id());
    		pstmt.setObject(8, product.getFileName());
    		pstmt.setObject(9, 0);

    		result = pstmt.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	super.closeAll(conn, pstmt, rs);
        }
    	return result;
	}

	@Override
	public int update(Product product) {
		int result=0;
    	try {
    		
    		conn = super.getConnection();
    		
    		String sql=" update easybuy_product set name=?,description=?,price=?,stock=?,categoryLevel1Id=?,categoryLevel2Id=?,categoryLevel3Id=?,fileName=?,isDelete=? where id=?  ";
    		
    		pstmt = conn.prepareStatement(sql);
    		
    		pstmt.setObject(1, product.getName());
    		pstmt.setObject(2, product.getDescription());
    		pstmt.setObject(3, product.getPrice());
    		pstmt.setObject(4, product.getStock());
    		pstmt.setObject(5, product.getCategoryLevel1Id());
    		
    		pstmt.setObject(6, product.getCategoryLevel2Id());
    		pstmt.setObject(7, product.getCategoryLevel3Id());
    		pstmt.setObject(8, product.getFileName());
    		pstmt.setObject(9, 0);
    		
    		pstmt.setObject(10, product.getId());

    		result = pstmt.executeUpdate();
            
        } catch (Exception e) {
            e.printStackTrace();
        }finally{
        	super.closeAll(conn, pstmt, rs);
        }
    	return result;
	}


}
